package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.jdbc.Statement;
import com.proactive.webapp.dao.CardTemplateDAO;
import com.proactive.webapp.entity.CardTemplate;
import com.proactive.webapp.entity.CourseCategory1;

public class CardTemplateDAOImpl implements CardTemplateDAO{
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public List<CardTemplate> getListByStatus(int activityID, int status, int start, int size) {
		// TODO Auto-generated method stub
		List<CardTemplate> cardTemplateList = new ArrayList<CardTemplate>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM card b WHERE a.cardTemplateID = b.cardTemplateID) "
				+ "AS useCount FROM card_template a WHERE activityID = ? AND cardTemplateStatus = ? "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, activityID);
			smt.setInt(2, status);
			smt.setInt(3, start);
			smt.setInt(4, size);
			rs = smt.executeQuery();
			while(rs.next()){
				CardTemplate cardTemplate = new CardTemplate();
				cardTemplate.setId(rs.getInt("cardTemplateID"));
				cardTemplate.setTitle(rs.getString("cardTemplateTitle"));
				cardTemplate.setImagePath(rs.getString("cardTemplateImage"));
				cardTemplate.setUrl(rs.getString("cardTemplateUrl"));
				cardTemplate.setHtml1Path(rs.getString("cardTemplateHtml1"));
				cardTemplate.setUseCount(rs.getInt("useCount"));
				cardTemplateList.add(cardTemplate);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return cardTemplateList;
	}

	@Override
	public int countTotalByStatus(int activityID, int status) {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM card_template "
				+ "WHERE activityID = ? AND cardTemplateStatus = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, activityID);
			smt.setInt(2, status);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public CardTemplate getByStatus(CardTemplate cardTemplate) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM card_template WHERE cardTemplateID = ? AND cardTemplateStatus = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, cardTemplate.getId());
			smt.setInt(2, cardTemplate.getStatus());
			rs = smt.executeQuery();
			while(rs.next()){
				cardTemplate.setTitle(rs.getString("cardTemplateTitle"));
				cardTemplate.setImagePath(rs.getString("cardTemplateImage"));
				cardTemplate.setUrl(rs.getString("cardTemplateUrl"));
				cardTemplate.setHtml1Path(rs.getString("cardTemplateHtml1"));
				cardTemplate.setHtml2Path(rs.getString("cardTemplateHtml2"));
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return cardTemplate;
		
	}

	public List<CardTemplate> getList(int start, int size){
		List<CardTemplate> cardTemplateList = new ArrayList<CardTemplate>();
		String sql = "SELECT * FROM card_template WHERE cardTemplateStatus != 3 "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, start);
			smt.setInt(2, size);
			rs = smt.executeQuery();
			while(rs.next()){
				CardTemplate cardtemplate = new CardTemplate();
				cardtemplate.setId(rs.getInt("cardTemplateID"));
				cardtemplate.setTitle(rs.getString("cardTemplateTitle"));
				cardtemplate.setStatus(rs.getInt("cardTemplateStatus"));
				cardTemplateList.add(cardtemplate);
			}
			rs.close();
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return cardTemplateList;
	}
	
	public CardTemplate get(CardTemplate cardTemplate){
		String sql = "SELECT * FROM card_template WHERE cardTemplateID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, cardTemplate.getId());
			rs = smt.executeQuery();
			if(rs.next()){		
				cardTemplate.setId(rs.getInt("cardTemplateID"));
				cardTemplate.setAid(rs.getInt("activityID"));
				cardTemplate.setTitle(rs.getString("cardTemplateTitle"));
				cardTemplate.setImagePath(rs.getString("cardTemplateImage"));
				cardTemplate.setUrl(rs.getString("cardTemplateUrl"));
				cardTemplate.setHtml1Path(rs.getString("cardTemplateHtml1"));
				cardTemplate.setHtml2Path(rs.getString("cardTemplateHtml2"));
				cardTemplate.setStatus(rs.getInt("cardTemplateStatus"));
				cardTemplate.setShow(cardTemplate.getStatus()==1?true:false);
			}
			rs.close();
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return cardTemplate;
	}

	public int insert(CardTemplate cardTemplate) {
		String sql = "INSERT INTO card_template(activityID, cardTemplateTitle, cardTemplateImage, "
				+ "cardTemplateUrl, cardTemplateHtml1, cardTemplateHtml2, cardTemplateStatus, createDate) VALUES"
				+ "(?,?,?,?,?,?,?,NOW())";
		int cardTemplateID = 0; 
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			smt.setInt(1, cardTemplate.getAid());
			smt.setString(2, cardTemplate.getTitle());
			smt.setString(3, cardTemplate.getImagePath());
			smt.setString(4, cardTemplate.getUrl());
			smt.setString(5, cardTemplate.getHtml1Path());
			smt.setString(6, cardTemplate.getHtml2Path());
			smt.setInt(7, cardTemplate.getStatus());
			smt.executeUpdate();
			rs = smt.getGeneratedKeys();
			if(rs.next()){
				cardTemplateID = rs.getInt(1);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return cardTemplateID;
	}
	
	public void delete(CardTemplate cardTemplate){
		String sql = "UPDATE card_template SET cardTemplateStatus=3 WHERE cardTemplateID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, cardTemplate.getId());
			smt.executeUpdate();
			smt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}	
	}
	
	public void update(CardTemplate cardTemplate) {
		// TODO Auto-generated method stub
		String sql = "UPDATE card_template SET activityID=?, cardTemplateTitle=?, cardTemplateImage=?, "
				+ "cardTemplateUrl=?, cardTemplateHtml1=?, cardTemplateHtml2=?, cardTemplateStatus=? "
				+ " WHERE cardTemplateID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, cardTemplate.getAid());
			smt.setString(2, cardTemplate.getTitle());
			smt.setString(3, cardTemplate.getImagePath());
			smt.setString(4, cardTemplate.getUrl());
			smt.setString(5, cardTemplate.getHtml1Path());
			smt.setString(6, cardTemplate.getHtml2Path());
			smt.setInt(7, cardTemplate.getStatus());
			smt.setInt(8, cardTemplate.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public int countTotal() {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM card_template WHERE cardTemplateStatus != 3";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}
}
